﻿/**********************************************************************************
 * 作者：Darren Zhou
 * 小组：开发小组
 * 说明： SQLiteHelper数据库助手类
 *创建日期：2017-07-28 00:23:04
 * 版本号：V1.0.0
* *********************************************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Configuration;
using System.Data.Common;
using Common.DataBase.Interface;
using Common.DataBase.Model;
using System.Reflection;
using CommonUtils.Attributes;

namespace Common.DataBase.Implement {
  public class SQLiteHelper:SQLHelper {

    private static log4net.ILog _logger = log4net.LogManager.GetLogger(typeof(SQLiteHelper));
    

    /// <summary>
    /// 数据库类型
    /// </summary>
    public override DataBaseType DBType {
      get {
        return DataBaseType.SQLite;
      }
    }

    
    protected override void PrepareConnection() {
      connectString = ConfigurationManager.AppSettings["appDataBase"];
      if (string.IsNullOrWhiteSpace(connectString)) {
        connectString = ConfigurationManager.ConnectionStrings["appDataBase"].ConnectionString;
      }
      queryUserTableSQL = "SELECT name as TableName FROM sqlite_master WHERE type='table' AND name != 'sqlite_sequence' ORDER BY name;";
      queryColumnsByTableNameSQL = "PRAGMA  table_info({0});";

      //sqlitePassword = ConfigurationManager.AppSettings["sqlitePassword"];
      //connstr = new SQLiteConnectionStringBuilder();
      //connstr.DataSource = connectString;
      //connstr.Password = sqlitePassword;
      //connection.ConnectionString = connstr.ToString();
      connection = new SQLiteConnection(connectString);
    }


    public SQLiteHelper() {
      PrepareConnection();   

      dataTypeMap["BIGINT"] = "System.Int64";
      dataTypeMap["BIGUINT"] = "System.UInt64";
      dataTypeMap["BINARY"] = "System.Byte[]";
      dataTypeMap["BIT"] = "System.Boolean";
      dataTypeMap["BLOB"] = "System.Byte[]";
      dataTypeMap["BOOL"] = "System.Boolean";
      dataTypeMap["BOOLEAN"] = "System.Boolean";
      dataTypeMap["CHAR"] = "System.String";
      dataTypeMap["CLOB"] = "System.String";
      dataTypeMap["COUNTER"] = "System.Int64";
      dataTypeMap["CURRENCY"] = "System.Decimal";
      dataTypeMap["DATE"] = "System.DateTime";
      dataTypeMap["DATETIME"] = "System.DateTime";
      dataTypeMap["DECIMAL"] = "System.Decimal";
      dataTypeMap["DOUBLE"] = "System.Double";
      dataTypeMap["FLOAT"] = "System.Double";
      dataTypeMap["GENERAL"] = "System.Byte[]";
      dataTypeMap["GUID"] = "System.Guid";
      dataTypeMap["IDENTITY"] = "System.Int64";
      dataTypeMap["IMAGE"] = "System.Byte[]";
      dataTypeMap["INT"] = "System.Int32";
      dataTypeMap["INT8"] = "System.SByte";
      dataTypeMap["INT16"] = "System.Int16";
      dataTypeMap["INT32"] = "System.Int32";
      dataTypeMap["INT64"] = "System.Int64";
      dataTypeMap["INTEGER"] = "System.Int64";
      dataTypeMap["INTEGER8"] = "System.SByte";
      dataTypeMap["INTEGER16"] = "System.Int16";
      dataTypeMap["INTEGER32"] = "System.Int32";
      dataTypeMap["INTEGER64"] = "System.Int64";
      dataTypeMap["LOGICAL"] = "System.Boolean";
      dataTypeMap["LONG"] = "System.Int64";
      dataTypeMap["LONGCHAR"] = "System.String";
      dataTypeMap["LONGTEXT"] = "System.String";
      dataTypeMap["LONGVARCHAR"] = "System.String";
      dataTypeMap["MEMO"] = "System.String";
      dataTypeMap["MONEY"] = "System.Decimal";
      dataTypeMap["NCHAR"] = "System.String";
      dataTypeMap["NOTE"] = "System.String";
      dataTypeMap["NTEXT"] = "System.String";
      dataTypeMap["NUMBER"] = "System.Decimal";
      dataTypeMap["NUMERIC"] = "System.Decimal";
      dataTypeMap["NVARCHAR"] = "System.String";
      dataTypeMap["OLEOBJECT"] = "System.Byte[]";
      dataTypeMap["RAW"] = "System.Byte[]";
      dataTypeMap["REAL"] = "System.Double";
      dataTypeMap["SINGLE"] = "System.Single";
      dataTypeMap["SMALLDATE"] = "System.DateTime";
      dataTypeMap["SMALLINT"] = "System.Int16";
      dataTypeMap["SMALLUINT"] = "System.UInt16";
      dataTypeMap["STRING"] = "System.String";
      dataTypeMap["TEXT"] = "System.String";
      dataTypeMap["TIME"] = "System.DateTime";
      dataTypeMap["TIMESTAMP"] = "System.DateTime";
      dataTypeMap["TINYINT"] = "System.Byte";
      dataTypeMap["TINYSINT"] = "System.SByte";
      dataTypeMap["UINT"] = "System.UInt32";
      dataTypeMap["UINT8"] = "System.Byte";
      dataTypeMap["UINT16"] = "System.UInt16";
      dataTypeMap["UINT32"] = "System.UInt32";
      dataTypeMap["UINT64"] = "System.UInt64";
      dataTypeMap["ULONG"] = "System.UInt64";
      dataTypeMap["UNIQUEIDENTIFIER"] = "System.Guid";
      dataTypeMap["UNSIGNEDINTEGER"] = "System.UInt64";
      dataTypeMap["UNSIGNEDINTEGER8"] = "System.Byte";
      dataTypeMap["UNSIGNEDINTEGER16"] = "System.UInt16";
      dataTypeMap["UNSIGNEDINTEGER32"] = "System.UInt32";
      dataTypeMap["UNSIGNEDINTEGER64"] = "System.UInt64";
      dataTypeMap["VARBINARY"] = "System.Byte[]";
      dataTypeMap["VARCHAR"] = "System.String";
      dataTypeMap["VARCHAR2"] = "System.String";
      dataTypeMap["YESORNO"] = "System.Boolean";
      dataTypeMap["YESNO"] = "System.Boolean";
    }

    /// <summary>
    /// 构造SQL参数
    /// </summary>
    /// <param name="parameterName">参数名</param>
    /// <param name="value">参数值</param>
    /// <returns>参数对象</returns>
    public override DbParameter DbParam(string parameterName, object value) {
      return new SQLiteParameter(parameterName,value);
    }

    
    /// <summary>
    /// 执行带参数的增删改SQL语句或存储过程
    /// </summary>
    /// <param name="commandText">要执行的SQL语句</param>
    /// <param name="commandType">命令类型</param>
    /// <param name="parameters">参数集合</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns>返回受影响的记录数</returns>
    public override int ExecuteNonQuery( string commandText, CommandType commandType, DbParameter[] parameters = null, bool isClose = true) {
      int effectiveRowCount = 0;
      try {
        OpenConnection();
        SQLiteCommand command = new SQLiteCommand( commandText, (SQLiteConnection)connection);
        command.CommandType = commandType;
        if(parameters != null && parameters.Length > 0) {
          command.Parameters.AddRange(parameters);
        }
        effectiveRowCount = command.ExecuteNonQuery();
      } catch(Exception exception) {
        foreach(DbParameter parmeter in parameters) {
          commandText = commandText.Replace(parmeter.ParameterName, string.Format("'{0}'", parmeter.Value));
        }
        _logger.Debug(commandText);
        _logger.Error( exception );
      } finally {
        if (isClose == true) {
          CloseConnection();
        }        
      }
      return effectiveRowCount;
    }



    
    /// <summary>
    /// 执行带参数的SQL查询语句
    /// </summary>
    /// <param name="commandText">要执行的SQL语句</param>
    /// <param name="commandType">命令类型</param>
    /// <param name="parameters">参数集合</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns></returns>
    public override DataTable ExecuteQuery( string commandText, CommandType commandType, DbParameter[] parameters, bool isClose = true) {
      DataTable dataTable = new DataTable();
      try {
        OpenConnection();
        SQLiteCommand command = new SQLiteCommand( commandText, (SQLiteConnection)connection );
        command.CommandType = commandType;
        if(parameters != null && parameters.Length > 0) {
          command.Parameters.AddRange(parameters);
        }
        using(SQLiteDataAdapter dataAdapter = new System.Data.SQLite.SQLiteDataAdapter( command )) {
          DataSet dataSet = new DataSet();
          dataAdapter.FillSchema( dataSet, SchemaType.Source );
          dataAdapter.Fill( dataSet );    //加载表数据
          dataTable = dataSet.Tables[0];
        }

        //using (dataReader = command.ExecuteReader(CommandBehavior.CloseConnection))
        //{
        //    dataTable.Load(dataReader);

        //}
      } catch(Exception exception) {
        foreach(DbParameter parmeter in parameters) {
          commandText = commandText.Replace(parmeter.ParameterName, string.Format("'{0}'", parmeter.Value));
        }
        _logger.Debug(commandText);
        _logger.Error( exception );
        dataTable = null;
      } finally {
        if (isClose == true) {
          CloseConnection();
        }
      }
      return dataTable;
    }

  


    /// <summary>
    /// 从数据库中检索单个值
    /// </summary>
    /// <param name="commandText">要执行的SQL语句</param>
    /// <param name ="commandType">命令类型</param>
    /// <param name="parameters">参数集合</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns>返回值是object类型</returns>
    public override object ExecuteScalar(string commandText, DbParameter[] parameters, CommandType commandType , bool isClose = true) {
      object result = null;
      try {
        OpenConnection();
        SQLiteCommand command = new SQLiteCommand(commandText, (SQLiteConnection)connection);
        command.CommandType = commandType;
        if(parameters != null && parameters.Length > 0) {
          command.Parameters.AddRange(parameters);
        }
        result = command.ExecuteScalar();
      } catch(Exception exception) {
        foreach(DbParameter parmeter in parameters) {
          commandText = commandText.Replace(parmeter.ParameterName, string.Format("'{0}'", parmeter.Value));
        }
        _logger.Debug(commandText);
        _logger.Error(exception);
      } finally {
        if(isClose == true) {
          CloseConnection();
        }
      }
      return result;
    }



    #region 批量导入DataTable

    //public void BulkToDB(string tableName, DataTable dt)
    //{
    //    SQLiteConnection sqlConn = new SQLiteConnection(
    //        ConfigurationManager.ConnectionStrings["connStr"].ConnectionString);
    //    sqlite
    //    SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);
    //    bulkCopy.DestinationTableName = tableName;
    //    bulkCopy.BatchSize = dt.Rows.Count;

    //    try
    //    {
    //        sqlConn.Open();
    //        if (dt != null && dt.Rows.Count != 0)
    //            bulkCopy.WriteToServer(dt);
    //    }
    //    catch (Exception ex)
    //    {
    //        throw ex;
    //    }
    //    finally
    //    {
    //        sqlConn.Close();
    //        if (bulkCopy != null)
    //            bulkCopy.Close();
    //    }
    //}

    #endregion 批量导入DataTable
  }
}
